/**
 *
 * MySQL操作封装类
 *
 * 对mysql2模块进行数据库操作二次封装
 *
 */

const MySQL = require('mysql2')

class mysql {

	/**
	 *
	 * 构造函数
	 *
	 * @param [class] common 工具库
	 * @param [object] config 配置数据
	 *
	 */

	constructor(common, config) {
		this.common = common
		this.config = config
		let {host, port, user, pwd, name, charset} = this.config
		this.db = MySQL.createPool({host, port, user, password: pwd, database: name, charset})
		this.db.on('connection', () => {
			console.log(`mysql db ${this.db.name} is connected`)
		})
		this.db.on('release', () => {
			console.log(`mysql db ${this.db.name} is release`)
		})
		this.db.name = name
	}

	/**
	 *
	 * 错误处理器
	 *
	 * @param [string] name 连接的数据库名称
	 * @param [object] err 数据库操作错误内容
	 *
	 */

	errHandler(name, err) {
		let msg = ''
		switch(err.code) {
			case 'PROTOCOL_CONNECTION_LOST':
			case 'ECONNRESET':
			case 'ECONNREFUSED':
			case 'EPIPE':
				msg = `${name} connect failed`
			break
			case 'ER_BAD_DB_ERROR':
				msg = err.sqlMessage
			break
			case 'ER_DATA_TOO_LONG':
				msg = 'insert data too long'
			break
			default:
				console.error(err)
				msg = `${name} error`
		}
		console.error(`mysql db ${msg}`)
		this.common.alertMsg('MySQL连接挂啦', msg)
		this.common.writeLog('error', `mysql db ${msg}`)
	}

	resolve(data = 0) {
		return new Promise((resolve) => {
			resolve(data)
		})
	}

	reject(code, msg) {
		return new Promise((resolve, reject) => {
			reject({
				code,
				msg
			})
		})
	}

	/**
	 *
	 * 检查连接状态并返回连接实例
	 *
	 * @param [string] name 连接的数据库名称
	 *
	 * @return [string] name 连接的数据库名称
	 *
	 */

	getConn() {
		return new Promise((resolve, reject) => {
			this.db.getConnection((err, connection) => {
				if(err) {
					this.errHandler(this.db.name, err)
					reject({
						code: '-1005',
						msg: 'database connection failed'
					})
					return
				}
				resolve(connection)
			})
		})
	}

	/**
	 *
	 * 关闭数据库连接
	 *
	 */

	async close() {
		this.db.end()
		console.error(`${this.db.name} is closed`)
		return this.resolve()
	}

	/**
	 *
	 * 执行语句操作
	 *
	 * @param [string] sql SQL语句
	 * @param [array] data 参数数组
	 *
	 * @method db.query('select * from table')
	 * @method db.query('select * from table where column = ?', [1])
	 *
	 * @return [array] result 查询的结果数组
	 *
	 */

	query(sql, data, err) {
		return new Promise( async (resolve, reject) => {
			try {
				let db = await this.getConn()
				console.log(sql, data)
				db.query(sql, data, (_err, result) => {
					if(_err) {
						console.error(`query sql failed:${sql} `)
						this.errHandler(this.db.name, _err)
						reject(err || {
							code: '-1006',
							msg: 'database query failed'
						})
					}
					else {
						db.release()
						resolve(result)
					}
				})
			}
			catch(err) {
				console.error(`query sql failed ${err.sql}`)
				this.errHandler(this.db.name, err)
				reject({
					code: '-1006',
					msg: 'database query failed'
				})
			}
		})
	}

	async createTable(table, fieldsData = [], tableAttrs = {}) {
		if(fieldsData.length == 0)
			return this.reject('-1065', 'data table must be have fields')
		let fieldsStr  = ''
		for(let fields of fieldsData) {
			fieldsStr += `${fields},`
		}
		fieldsStr = this.common.rtrim(fieldsStr, ',')
		await this.query("CREATE TABLE IF NOT EXISTS `" + table + "` (" + fieldsStr + ") ENGINE=InnoDB" + (tableAttrs.autoId ? ' AUTO_INCREMENT=1 ' : ' ') + "DEFAULT CHARSET=" + (tableAttrs.charset || 'utf8mb4') + " COLLATE=" + (tableAttrs.collate || 'utf8mb4_bin') + " ROW_FORMAT=" + (tableAttrs.rowFormat || 'COMPACT') + " COMMENT='" + (tableAttrs.comment || '') + "'")
	}

	async tableExists(table) {
		const result = await this.query(`show tables like '${table}'`)
		if(result[0])
			return true
		else
			return false
	}

	/**
	 *
	 * 插入操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] fields 插入的字段名，用,分割
	 * @param [array] data 参数数组
	 *
	 * @method db.insert('tableName', 'insertColumn1, insertColumn2, ...', ['001', 'test', ...])
	 * @method db.insert('tableName', 'insertColumn1, insertColumn2, ...', [['001', 'test', ...], ['002', 'test1', ...]])
	 *
	 * @return [number] 成功插入的条数
	 *
	 */

	async insert(table, fields, data) {
 		try {
 			let db = this.db
 			let groups = []
 			if(data instanceof Array && data[0] instanceof Array) {
 				let _data = []
 				for(let data1 of data) {
 					for(let data2 of data1) {
 						_data.push(data2)
 					}
 					groups.push(data1.length)
 				}
 				data = _data;
 			}
 			else {
 				groups.push(data.length)
 			}
 			let groupVal = '';
 			let vals = '';
 			for(let group of groups) {
 				for(let i = 0;i < group;i++) {
 					vals += ',?'
 				}
 				vals = vals.substr(1, vals.length)
 				groupVal += `,(${vals})`
 				vals = ''
 			}
 			groupVal = groupVal.substr(1, groupVal.length)
 			let sql = `insert into ${table}(${fields}) values${groupVal}`
			const result = await this.query(sql, data, {
				code: '-1007',
				msg: 'database insert failed'
			})
			return this.resolve(result.affectedRows)
 		}
 		catch(err) {
 			console.error(`insert data failed ${err.sql}`, err)
			return this.reject('-1007', 'database insert failed')
 		}
	 }

	/**
	 *
	 * 删除操作
	 *
	 * @param [string] table 数据表名
	 * @param [string] where 条件语句字符串
	 * @param [array] data 参数数组
	 *
	 * @mehtod db.delete('tableName', 'tableColumn1 = ? or tableColumn12= ? or ...', ['001', '002', ...])
	 *
	 * @return [number] 成功删除的条数
	 *
	 */
	
	async delete(table, where, data) {
		try {
			let db = this.db
			let sql = `delete from ${table} where ${where}`
			let result = await this.query(sql, data, {
				code: '-1008',
				msg: 'database delete failed'
			})
			return this.resolve(result.affectedRows)
		}
		catch(err) {
			console.error(`delete data failed ${sql}`, err)
			return this.reject('-1008', 'database delete failed')
		}
	}

	/**
	 *
	 * 查询操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] fields 查询的字段名，用,分割
	 * @param [string] where 条件字符串
	 * @param [array] data 参数数组
	 * @param [string] sort 排序字符串
	 * @param [string] limit 截取条数字符串
	 *
	 * @method db.select('tableName')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...', 'tableColumn1 = 'test', tableColumn2 = 1, ...')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...', 'tableColumn1 = ?, tableColumn2 = ?, ...', ['value1', 'value2', ...])
	 *
	 * @return [array] 查询的结果数组
	 *
	 */

	async select(table, fields = '*', where, data, sort, limit) {
		try {
			let db = this.db
			where = where ? ` where ${where}` : ''
			sort = sort ? ` order by ${sort}` : ''
			limit = limit ? ` limit ${limit}` : ''
			let sql = `select ${fields} from ${table}${where}${sort}${limit};`;
			const result = await this.query(sql, data, {
				code: '-1009',
				msg: 'database select failed'
			})
			return this.resolve(result)
		}
		catch(err) {
			console.error(`select data failed ${err.sql}`, err)
			return this.reject('-1009', 'database select failed')
		}
	}

	/**
	 *
	 * 修改操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] updateFields 更新的字段名，用,分开
	 * @param [string] where 条件字符串
	 * @param [array] data 参数数组
	 *
	 * @method db.update('tableName', 'tableUpdateColumn1=123', 'tableColumn1=1')
	 * @method db.update('tableName', 'tableUpdateColumn1=？, ...', 'tableColumn1=?, ...', [10, 1, ...])
	 *
	 * @return [number] 成功修改的条数
	 *
	 */

	async update(table, updateFields, where, data) {
		try {
			let db = this.db
			let sql = `update ${table} set ${updateFields} where ${where}`
			const result = await this.query(sql, data, {
				code: '-1010',
				msg: 'database update failed'
			})
			return this.resolve(result.affectedRows)
		}
		catch(err) {
			console.error(`update data failed ${err.sql}`, err)
			return this.reject('-1010', 'database update failed')
		}
	}

}

module.exports = mysql